package com.zr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.model.HostelModel;
import com.zr.model.UserModel;
import com.zr.util.JdbcUtil;

/**
 * 寝室管理系统
 * @author wxz
 * 2016年5月26日上午9:59:34
 */
public class HostelSelectDao {
	/**
	 * 查询寝室信息
	 * @param pagestart 起始页面数
	 * @param pagerows 每页显示行数
	 * @return
	 */
	public List<HostelModel> getHostel(int pagestart,int pagerows){
		List<HostelModel> hostellist=new ArrayList<>();
		String sql="select user.hid,GROUP_CONCAT(sname) names,hbuiding,COUNT(sid) count,hnumber from user,hostel where user.hid=hostel.hid GROUP BY hid limit ?,?";
		Connection con=null;
		try {
			con=JdbcUtil.getDBConnection();
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setInt(1, pagestart);
			pst.setInt(2, pagerows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				HostelModel hModel = new HostelModel();
				hModel.setHid(rs.getInt("hid"));
				hModel.setHname(rs.getString("names"));
				hModel.setHbuiding(rs.getString("hbuiding"));
				hModel.setHnumber(rs.getInt("hnumber"));
				hModel.setCount(rs.getInt("count"));
				hostellist.add(hModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.closedConnection(con);
		}
		return hostellist;
	}
	/**
	 * 寝室数量
	 * @return
	 */
	public int getHostelCount(){
		int count = 0;
		String sql="select count(*) count from (select user.hid,GROUP_CONCAT(sname) from user,hostel where user.hid=hostel.hid GROUP BY hid)a";
		Connection con=null;
		try {
			con=JdbcUtil.getDBConnection();
			PreparedStatement pst=con.prepareStatement(sql);
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				count=rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.closedConnection(con);
		}
		return count;
	}
	/**
	 * 判断学生是否存在若存在则修改学生hid
	 * @param sid 学号
	 * @param sname 姓名
	 * @param targetHid 要修改的hid 
	 * @return
	 */
	public boolean getSelectHostelUpdate(int sid,String sname,int targetHid){
		boolean flag=false;
		String sql="select * from user where user.sid=? and user.sname=?";
		Connection con=null;
		try {
			con=JdbcUtil.getDBConnection();
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setInt(1, sid);
			pst.setString(2, sname);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				String sql2="update user set hid=? where sid=? and sname=?";
				PreparedStatement pst2=con.prepareStatement(sql2);
				pst2.setInt(1, targetHid);
				pst2.setInt(2, sid);
				pst2.setString(3, sname);
				if(pst2.executeUpdate()!=0){
					flag=true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
}
